<%@ page pageEncoding="UTF-8" %>
<%@page import="java.util.Map"%>
<%@page import="java.util.List"%>
<%@ include file="conndb.jsp"%>
<%!

/**帮助方法获取集合**/
private List<Map<String,Object>> parseList(ResultSet rs){
	List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
	try{
		while(rs.next()){
			Map<String,Object> m = parseMap(rs);
			if(null!=m)
				list.add(m);
		}
		return list;
	}catch(Exception e){
		e.printStackTrace();
	}
    return null;
}

/**帮助方法获取Map集合**/
private Map<String,Map<String,Object>> parseMapList(ResultSet rs,String fieldKeyId){
	Map<String,Map<String,Object>> mapList = new HashMap<String,Map<String,Object>>();
	try{
		while(rs.next()){
			Map<String,Object> m = parseMap(rs);
			if(null!=m)
				mapList.put((String)m.get(fieldKeyId),m);
		}
		return mapList;
	}catch(Exception e){
		e.printStackTrace();
	}
    return null;
}


/**帮助方法获取Map形式的对象**/
private Map<String,Object> parseMap(ResultSet rs){
	Map<String,Object> m = new HashMap<String,Object>();
	try{
		ResultSetMetaData rsmd = rs.getMetaData();
		int cc = rsmd.getColumnCount();
		for(int j=0;j<cc;j++){
			if(null!=rsmd.getColumnName(j+1)){
				if(rsmd.getColumnType(j+1)==2005){
					m.put(rsmd.getColumnName(j+1).toLowerCase(),rs.getString(rsmd.getColumnName(j+1)));
				}else if (rsmd.getColumnType(j+1)==91){
					m.put(rsmd.getColumnName(j+1).toLowerCase(),rs.getTimestamp(rsmd.getColumnName(j+1)));
				}else{
					m.put(rsmd.getColumnName(j+1).toLowerCase(),rs.getObject(rsmd.getColumnName(j+1)));
				}
			}
		}
		return m;
	}catch(Exception e){
		e.printStackTrace();
	}
	return null;	
}

/*#########################以上为私有方法###################################*/

public Map<String,Object> getObject(String sql){
	Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	try{
		conn=getConnection();
		pst=conn.prepareStatement(sql);
		rs=pst.executeQuery();
		//获取集合列表
		if(rs.next()){
			Map<String,Object> m = parseMap(rs);
			return m;
		}
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		closeConn(conn,pst,rs);
	}
    return null;
}

public Map<String,Object> getObject(String sql,Object[] params){
	Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	try{
		conn=getConnection();
		pst=conn.prepareStatement(sql);
		if(null!=params){
			for(int i=0; i<params.length;i++){
				pst.setObject(i+1,params[i]);
			}
		}
		rs=pst.executeQuery();
		//获取集合列表
		if(rs.next()){
			Map<String,Object> m = parseMap(rs);
			return m;
		}
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		closeConn(conn,pst,rs);
	}
    return null;
}

public Map<String,Object> getObject(String sql,List<Object> params){
	Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	try{
		conn=getConnection();
		pst=conn.prepareStatement(sql);
		if(null!=params){
			for(int i=0; i<params.size();i++){
				pst.setObject(i+1,params.get(i));
			}
		}
		rs=pst.executeQuery();
		//获取集合列表
		if(rs.next()){
			Map<String,Object> m = parseMap(rs);
			return m;
		}
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		closeConn(conn,pst,rs);
	}
    return null;
}

public Map<String,Map<String,Object>> queryForMapList(String sql,String fieldKeyId) {
	Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	try{
		conn=getConnection();
		pst=conn.prepareStatement(sql);
		rs=pst.executeQuery();
		//获取集合列表
		Map<String,Map<String,Object>> mapList = parseMapList(rs,fieldKeyId);
		return mapList;
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		closeConn(conn,pst,rs);
	}
    return null;
}

public Map<String,Map<String,Object>> queryForMapList(String sql,Object[] params,String fieldKeyId) {
	Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	try{
		conn=getConnection();
		pst=conn.prepareStatement(sql);
		if(null!=params){
			for(int i=0; i<params.length;i++){
				pst.setObject(i+1,params[i]);
			}
		}
		rs=pst.executeQuery();
		//获取集合列表
		Map<String,Map<String,Object>> mapList = parseMapList(rs,fieldKeyId);
		return mapList;
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		closeConn(conn,pst,rs);
	}
    return null;
}

public List<Map<String,Object>> queryForList(String sql) {
	Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	try{
		conn=getConnection();
		pst=conn.prepareStatement(sql);
		
		rs=pst.executeQuery();
		//获取集合列表
		List<Map<String,Object>> list = parseList(rs);
		return list;
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		closeConn(conn,pst,rs);
	}
    return null;
}

public List<Map<String,Object>> queryForList(String sql,Object[] params) {
	Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	try{
		conn=getConnection();
		pst=conn.prepareStatement(sql);
		if(null!=params){
			for(int i=0; i<params.length;i++){
				pst.setObject(i+1,params[i]);
			}
		}
		rs=pst.executeQuery();
		//获取集合列表
		List<Map<String,Object>> list = parseList(rs);
		return list;
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		closeConn(conn,pst,rs);
	}
    return null;
}

public List<Map<String,Object>> queryForList(String sql,List<Object> params) {
	Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	try{
		conn=getConnection();
		pst=conn.prepareStatement(sql);
		if(null!=params){
			for(int i=0; i<params.size();i++){
				pst.setObject(i+1,params.get(i));
			}
		}
		rs=pst.executeQuery();
		//获取集合列表
		List<Map<String,Object>> list = parseList(rs);
		return list;
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		closeConn(conn,pst,rs);
	}
    return null;
}

/**
 * 分页查询
 * @param sql sql语句
 * @param params Object[]参数
 * @param currentPage 当前页数
 * @param pageSize 每页条数
**/
public List<Map<String,Object>> queryForListPage(String sql,Object[] params,int currentPage,int pageSize){
	if(pageSize==0){
		pageSize = 10;
	}
	String sql1 = "select tt3.* from (select rownum as r,tt2.* from (";
	String sql2 = ") tt2 where rownum<= ?) tt3 where tt3.r > ? ";
	if(currentPage<=0){
		currentPage = 1;
	}
	
	int start = (currentPage-1) * pageSize;
	int end = (currentPage-1) * pageSize +pageSize;
	
	String querySql = sql1 + sql + sql2;
	Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	try{
		conn=getConnection();
		pst=conn.prepareStatement(querySql);
		if(null!=params){
			for(int i=0; i<params.length;i++){
				pst.setObject(i+1,params[i]);
			}
			pst.setObject(params.length+1,end);
			pst.setObject(params.length+2,start);
		}else{
			pst.setObject(1,end);
			pst.setObject(2,start);
		}
		
		rs=pst.executeQuery();
		//获取集合列表
		List<Map<String,Object>> list = parseList(rs);
		return list;
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		closeConn(conn,pst,rs);
	}
    return null;
}

public List<Map<String,Object>> queryForListPage(String sql,List<Object> params,int currentPage,int pageSize){
	if(pageSize==0){
		pageSize = 10;
	}
	
	if(currentPage<=0){
		currentPage = 1;
	}
	
	int start = (currentPage-1) * pageSize;
	int end = (currentPage-1) * pageSize +pageSize;
	
	String sql1 = "select tttt3.* from (select rownum as r,tttt2.* from (";
	String sql2 = ") tttt2 where rownum<= "+end+") tttt3 where tttt3.r > "+start+" ";
	
	String querySql = sql1 + sql + sql2;
	Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	try{
		conn=getConnection();
		pst=conn.prepareStatement(querySql);
		if(null!=params){
			for(int i=0; i<params.size();i++){
				pst.setObject(i+1,params.get(i));
			}
		}
		
		rs=pst.executeQuery();
		//获取集合列表
		List<Map<String,Object>> list = parseList(rs);
		return list;
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		closeConn(conn,pst,rs);
	}
    return null;
}

/**
*分页用于查询总记录数
*@param 与分页查询sql一致
*@param params 与分页查询参数一致
*/
public int queryForPageCount(String sql,Object[] params){
	Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	try{
		conn=getConnection();
		sql = "select count(*) from ("+sql+")";
		pst=conn.prepareStatement(sql);
		if(null!=params){
			for(int i=0; i<params.length;i++){
				pst.setObject(i+1,params[i]);
			}
		}
		rs=pst.executeQuery();
		if(rs.next()){
			int count = rs.getInt(1);
			return count;
		}
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		closeConn(conn,pst,rs);
	}
    return 0;
}
/**
*分页用于查询总记录数
*@param 与分页查询sql一致
*@param params 与分页查询参数一致
*/
public int queryForPageCount(String sql,List<Object> params){
	Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	try{
		conn=getConnection();
		sql = "select count(*) from ("+sql+")";
		pst=conn.prepareStatement(sql);
		if(null!=params){
			for(int i=0; i<params.size();i++){
				pst.setObject(i+1,params.get(i));
			}
		}
		rs=pst.executeQuery();
		if(rs.next()){
			int count = rs.getInt(1);
			return count;
		}
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		closeConn(conn,pst,rs);
	}
    return 0;
}

/**
*分页用于查询总记录数
queryForPageCount 报错用这个
*@param 与分页查询sql一致
*@param params 与分页查询参数一致
*/
public int queryForPageCount2(String sql,Object[] params){
	Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	try{
		conn=getConnection();
		sql = "select count(rn) from (select ttcount.*,rownum rn from ("+sql+") ttcount)";
		pst=conn.prepareStatement(sql);
		if(null!=params){
			for(int i=0; i<params.length;i++){
				pst.setObject(i+1,params[i]);
			}
		}
		rs=pst.executeQuery();
		if(rs.next()){
			int count = rs.getInt(1);
			return count;
		}
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		closeConn(conn,pst,rs);
	}
    return 0;
}

/***
*	查询总记录数
* @param sql查询数量的语句
*/
public int queryCount(String sql,Object[] params){
	Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	try{
		conn=getConnection();
		pst=conn.prepareStatement(sql);
		if(null!=params){
			for(int i=0; i<params.length;i++){
				pst.setObject(i+1,params[i]);
			}
		}
		rs=pst.executeQuery();
		if(rs.next()){
			int count = rs.getInt(1);
			return count;
		}
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		closeConn(conn,pst,rs);
	}
    return 0;
}

//更新语句
public int executeUpdate(String sql,List<Object> params){
	Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	try{
		conn=getConnection();
		pst=conn.prepareStatement(sql);
		if(null!=params){
			for(int i=0; i<params.size();i++){
				pst.setObject(i+1,params.get(i));
			}
		}
		int count = pst.executeUpdate();
		return count;
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		closeConn(conn,pst,rs);
	}
    return 0;
}

//更新语句
public int executeUpdate(String sql,Object[] params){
	Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	try{
		conn=getConnection();
		pst=conn.prepareStatement(sql);
		if(null!=params){
			for(int i=0; i<params.length;i++){
				pst.setObject(i+1,params[i]);
			}
		}
		int count = pst.executeUpdate();
		return count;
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		closeConn(conn,pst,rs);
	}
    return 0;
}


//批量更新
public void executeUpdateBatch(String sql,List<List<Object>> paramsList){
    Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	try {  
// 	      Class.forName("com.mysql.jdbc.Driver");  
	      conn = getConnection();//DriverManager.getConnection(o_url, userName, password);  
	      conn.setAutoCommit(false);  
	      //sq//l = "INSERT adlogs(ip,website,yyyymmdd,hour,object_id) VALUES(?,?,?,?,?)";  
	      PreparedStatement prest = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);  
	      for(int i = 0; i < paramsList.size(); i++){
	          List<Object> params = paramsList.get(i);
	          for(int j=0;j<params.size();j++){
	              prest.setObject(j+1,params.get(j));
	          }
// 	         prest.setString(1, "192.168.1.1");  
// 	         prest.setString(2, "localhost");  
// 	         prest.setString(3, "20081009");  
// 	         prest.setInt(4, 8);  
// 	         prest.setString(5, "11111111");  
	         prest.addBatch();  
	      }  
	      prest.executeBatch();  
	      conn.commit();  
	      conn.close();  
	} catch (SQLException ex) {  
	    ex.printStackTrace();
	   //Logger.getLogger(MyLogger.class.getName()).log(Level.SEVERE, null, ex);  
	} finally{
		closeConn(conn,pst,rs);
	}
}
%>